﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Web.Configuration;
using Oracle.DataAccess.Client;
using Platform.Data;
using Entity;
using Access.BLL;
using System.Collections.Generic;

namespace YiWangTongBan
{
    /*
     * 定义一网通办数据库访问类
     * 创建人：方志恩
     * 创建日期：2019/11/22
     */
    public class AccessDataBase : WTB_YIHUDRKXXProcess
    {
        #region Construction
        protected string conn;
        public AccessDataBase() { conn = WebConfigurationManager.ConnectionStrings["BusinessConnString"].ToString(); }
        #endregion

        #region 主表
        public int InsertTB_YEWUZB(WTB_YEWUZB WTB_YEWUZB, OracleTransaction Transaction)
        {
            string spName = "CS_InsWTB_YEWUZB";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_YEWUZB.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_YEWUZB.CUSTOM_TYPE;
            storedParams[2].Value = WTB_YEWUZB.APPDATE;
            storedParams[3].Value = WTB_YEWUZB.CONTACT_VALUE;
            storedParams[4].Value = WTB_YEWUZB.CARD_ID;
            storedParams[5].Value = WTB_YEWUZB.APPLY_CONTENT;
            storedParams[6].Value = WTB_YEWUZB.COMPANY_NAME;
            storedParams[7].Value = WTB_YEWUZB.COMPANY_TYPE;
            storedParams[8].Value = WTB_YEWUZB.SOCIAL_CREDIT_CODE;
            storedParams[9].Value = WTB_YEWUZB.PHONE;
            storedParams[10].Value = WTB_YEWUZB.WYGZ;
            storedParams[11].Value = WTB_YEWUZB.BUSINESS_TYPE;
            storedParams[12].Value = WTB_YEWUZB.SHW_ADDRESS;
            storedParams[13].Value = WTB_YEWUZB.APPLICANT;
            storedParams[14].Value = WTB_YEWUZB.INCIDENT_TYPE;
            storedParams[15].Value = WTB_YEWUZB.CERT_TYPE;
            storedParams[16].Value = WTB_YEWUZB.MAIL;
            storedParams[17].Value = WTB_YEWUZB.APPLY_NO;
            storedParams[18].Value = WTB_YEWUZB.CERT_NUMBER;
            storedParams[19].Value = WTB_YEWUZB.INCIDENT_ID;
            storedParams[20].Value = WTB_YEWUZB.EMAIL;
            storedParams[21].Value = WTB_YEWUZB.ADDRESS;
            storedParams[22].Value = WTB_YEWUZB.CON_NAME;
            storedParams[23].Value = WTB_YEWUZB.D_CAOZUOSJ;
            storedParams[24].Value = WTB_YEWUZB.I_BEIYONG1;
            storedParams[25].Value = WTB_YEWUZB.I_BEIYONG2;
            storedParams[26].Value = WTB_YEWUZB.S_BEIYOGN1;
            storedParams[27].Value = WTB_YEWUZB.S_BEIYOGN2;
            storedParams[28].Value = WTB_YEWUZB.S_BEIYOGN3;
            storedParams[29].Value = WTB_YEWUZB.D_JIESHOUSJ;
            storedParams[30].Value = WTB_YEWUZB.D_HUITIANSJ;
            storedParams[31].Value = WTB_YEWUZB.D_BEIYOGN3;
            storedParams[32].Value = WTB_YEWUZB.I_ZHUANGTAI;
            storedParams[33].Value = WTB_YEWUZB.IS_TRANSFER_ORDER;
            storedParams[34].Value = WTB_YEWUZB.SHW_COMPANY_SOURCE;
            storedParams[35].Value = WTB_YEWUZB.BILL_ADDRESS;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_YEWUZB.ID = Convert.ToInt32(storedParams[36].Value);
            return result;
        }
        public int UPDWTB_YEWUZB(string incidentid,int zhuangtai, OracleTransaction Transaction)
        {
            string spName = "CS_UPDWTB_YEWUZB";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = incidentid;
            storedParams[1].Value = zhuangtai;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            return result;
        }

        #endregion

        #region 子表
        /*定义写入 子表_报修 的方法*/
        public int InsertWTB_BAOXIUXX(WTB_BAOXIUXX WTB_BAOXIUXX, OracleTransaction Transaction)
        {
            string spName = "CS_INSWTB_BAOXIUXX";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_BAOXIUXX.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_BAOXIUXX.INCIDENT_ID;
            storedParams[2].Value = WTB_BAOXIUXX.CASE_TYPE_CD;
            storedParams[3].Value = WTB_BAOXIUXX.FYLY;
            storedParams[4].Value = WTB_BAOXIUXX.FYXS;
            storedParams[5].Value = WTB_BAOXIUXX.FYQM;
            storedParams[6].Value = WTB_BAOXIUXX.FYLB;
            storedParams[7].Value = WTB_BAOXIUXX.FYNR;
            storedParams[8].Value = WTB_BAOXIUXX.BX_DTTM;
            storedParams[9].Value = WTB_BAOXIUXX.CLJB;
            storedParams[10].Value = WTB_BAOXIUXX.CN_DTTM;
            storedParams[11].Value = WTB_BAOXIUXX.BX_LOC_CD;
            storedParams[12].Value = WTB_BAOXIUXX.REPAIR_COMMENT;
            storedParams[13].Value = WTB_BAOXIUXX.I_BEIYONG1;
            storedParams[14].Value = WTB_BAOXIUXX.I_BEIYONG2;
            storedParams[15].Value = WTB_BAOXIUXX.I_BEIYONG3;
            storedParams[16].Value = WTB_BAOXIUXX.S_BEIYONG1;
            storedParams[17].Value = WTB_BAOXIUXX.S_BEIYONG2;
            storedParams[18].Value = WTB_BAOXIUXX.S_BEIYONG3;
            storedParams[19].Value = WTB_BAOXIUXX.D_BEIYONG1;
            storedParams[20].Value = WTB_BAOXIUXX.D_BEIYONG2;
            storedParams[21].Value = WTB_BAOXIUXX.D_BEIYONG3;
            storedParams[22].Value = WTB_BAOXIUXX.ID;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_BAOXIUXX.ID = Convert.ToInt32(storedParams[22].Value);
            return result;
        }

        /*定义写入 子表_单位接水&居民住宅单独接水 的方法*/
        public int InsertWTB_JIESHUIXX(WTB_JIESHUIXX WTB_JIESHUI, OracleTransaction Transaction)
        {
            string spName = "CS_INSWTB_JIESHUIXX";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_JIESHUI.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_JIESHUI.INCIDENT_ID;
            storedParams[2].Value = WTB_JIESHUI.S_TYPE;
            storedParams[3].Value = WTB_JIESHUI.CONTACT_ADDRESS;
            storedParams[4].Value = WTB_JIESHUI.S_PROJECT;
            storedParams[5].Value = WTB_JIESHUI.CONTACT_PERSON;
            storedParams[6].Value = WTB_JIESHUI.YSXQSC;
            storedParams[7].Value = WTB_JIESHUI.YSXQXF;
            storedParams[8].Value = WTB_JIESHUI.OVERALL_FLOORAGE;
            storedParams[9].Value = WTB_JIESHUI.QYZJLX;
            storedParams[10].Value = WTB_JIESHUI.QYZJBM;
            storedParams[11].Value = WTB_JIESHUI.FRXM;
            storedParams[12].Value = WTB_JIESHUI.FRSFZHM;
            storedParams[13].Value = WTB_JIESHUI.YDXZ;
            storedParams[14].Value = WTB_JIESHUI.BUILD_TYPE;
            storedParams[15].Value = WTB_JIESHUI.WYGZ;
            storedParams[16].Value = WTB_JIESHUI.KHXZ;
            storedParams[17].Value = WTB_JIESHUI.SQLB;
            storedParams[18].Value = WTB_JIESHUI.I_BEIYONG1;
            storedParams[19].Value = WTB_JIESHUI.I_BEIYONG2;
            storedParams[20].Value = WTB_JIESHUI.I_BEIYONG3;
            storedParams[21].Value = WTB_JIESHUI.S_BEIYONG1;
            storedParams[22].Value = WTB_JIESHUI.S_BEIYONG2;
            storedParams[23].Value = WTB_JIESHUI.S_BEIYONG3;
            storedParams[24].Value = WTB_JIESHUI.D_BEIYONG1;
            storedParams[25].Value = WTB_JIESHUI.D_BEIYONG2;
            storedParams[26].Value = WTB_JIESHUI.D_BEIYONG3;
            storedParams[27].Value = WTB_JIESHUI.FCZHM;
            storedParams[28].Value = WTB_JIESHUI.FENSUO;
            storedParams[29].Value = WTB_JIESHUI.XZQ;
            storedParams[30].Value = WTB_JIESHUI.JDZ;
            storedParams[31].Value = WTB_JIESHUI.APPLY_MEMO;
            storedParams[32].Value = WTB_JIESHUI.ID;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_JIESHUI.ID = Convert.ToInt32(storedParams[32].Value);
            return result;
        }

        /*定义写入 子表_居民过户/单位过户&实名制的方法*/
        public int InsertWTB_GUOHUXX(WTB_GUOHUXX WTB_GUOHUXX, OracleTransaction Transaction)
        {
            string spName = "CS_InsWTB_GUOHUXX";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_GUOHUXX.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_GUOHUXX.INCIDENT_ID;
            storedParams[2].Value = WTB_GUOHUXX.NEW_ENTITY_NAME;
            storedParams[3].Value = WTB_GUOHUXX.ID_TYPE;
            storedParams[4].Value = WTB_GUOHUXX.ID_NBR;
            storedParams[5].Value = WTB_GUOHUXX.FCZJLX;
            storedParams[6].Value = WTB_GUOHUXX.FCZJHM;
            storedParams[7].Value = WTB_GUOHUXX.ADDRESS;
            storedParams[8].Value = WTB_GUOHUXX.COMM_RTE_TYPE_CD;
            storedParams[9].Value = WTB_GUOHUXX.CONTACT_VALUE;
            storedParams[10].Value = WTB_GUOHUXX.GYSHTBH;
            storedParams[11].Value = WTB_GUOHUXX.FPLX;
            storedParams[12].Value = WTB_GUOHUXX.FPTT;
            storedParams[13].Value = WTB_GUOHUXX.NSRSBH;
            storedParams[14].Value = WTB_GUOHUXX.YSRKS;
            storedParams[15].Value = WTB_GUOHUXX.TRANSFER_COMMENT;
            storedParams[16].Value = WTB_GUOHUXX.TYPC;
            storedParams[17].Value = WTB_GUOHUXX.I_BEIYONG1;
            storedParams[18].Value = WTB_GUOHUXX.I_BEIYONG2;
            storedParams[19].Value = WTB_GUOHUXX.I_BEIYONG3;
            storedParams[20].Value = WTB_GUOHUXX.S_BEIYONG1;
            storedParams[21].Value = WTB_GUOHUXX.S_BEIYONG2;
            storedParams[22].Value = WTB_GUOHUXX.S_BEIYONG3;
            storedParams[23].Value = WTB_GUOHUXX.D_BEIYONG1;
            storedParams[24].Value = WTB_GUOHUXX.D_BEIYONG2;
            storedParams[25].Value = WTB_GUOHUXX.D_BEIYONG3;
            storedParams[26].Value = WTB_GUOHUXX.FYLY;
            storedParams[27].Value = WTB_GUOHUXX.FYXS;
            storedParams[28].Value = WTB_GUOHUXX.FYQM;
            storedParams[29].Value = WTB_GUOHUXX.ENTITY_NAME;
            storedParams[30].Value = WTB_GUOHUXX.REALNAME_COMMENT;
            storedParams[31].Value = WTB_GUOHUXX.METER_NUMBER;
            storedParams[32].Value = WTB_GUOHUXX.TRANSFER_REASON;
            storedParams[33].Value = WTB_GUOHUXX.USE_TYPE;
            storedParams[34].Value = WTB_GUOHUXX.CONTACTS;
            storedParams[35].Value = WTB_GUOHUXX.CONTACT_NUM;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_GUOHUXX.ID = Convert.ToInt32(storedParams[36].Value);
            return result;
        }

        /*定义写入 子表_居民住宅总表分装 的方法*/
        public int InsertWTB_JUMINZGFXX(WTB_JUMINZGFXX WTB_JUMINZGFXX, OracleTransaction Transaction)
        {
            string spName = "CS_INSWTB_JUMINZGFXX";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_JUMINZGFXX.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_JUMINZGFXX.INCIDENT_ID;
            storedParams[2].Value = WTB_JUMINZGFXX.YB;
            storedParams[3].Value = WTB_JUMINZGFXX.SPLIT_COMMENT;
            storedParams[4].Value = WTB_JUMINZGFXX.WYGZ;
            storedParams[5].Value = WTB_JUMINZGFXX.I_BEIYONG1;
            storedParams[6].Value = WTB_JUMINZGFXX.I_BEIYONG2;
            storedParams[7].Value = WTB_JUMINZGFXX.I_BEIYONG3;
            storedParams[8].Value = WTB_JUMINZGFXX.S_BEIYONG1;
            storedParams[9].Value = WTB_JUMINZGFXX.S_BEIYONG2;
            storedParams[10].Value = WTB_JUMINZGFXX.S_BEIYONG3;
            storedParams[11].Value = WTB_JUMINZGFXX.D_BEIYONG1;
            storedParams[12].Value = WTB_JUMINZGFXX.D_BEIYONG2;
            storedParams[13].Value = WTB_JUMINZGFXX.D_BEIYONG3;
            storedParams[14].Value = WTB_JUMINZGFXX.FENSUO;
            storedParams[15].Value = WTB_JUMINZGFXX.XZQ;
            storedParams[16].Value = WTB_JUMINZGFXX.JDZ;
            storedParams[17].Value = WTB_JUMINZGFXX.ID;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_JUMINZGFXX.ID = Convert.ToInt32(storedParams[17].Value);
            return result;
        }
        /*定义写入 一户多人口表 的方法*/
        public int InsertWTB_YIHUDRKXX(WTB_YIHUDRKXX WTB_YIHUDRKXX, OracleTransaction Transaction)
        {
            //string spName = "CS_InsWTB_YIHUDRKXX";
            //OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            //storedParams[0].Value = WTB_YIHUDRKXX.S_ZHUBIAOBH;
            //storedParams[1].Value = WTB_YIHUDRKXX.INCIDENT_ID;
            //storedParams[2].Value = WTB_YIHUDRKXX.CONTACT_NUM;
            //storedParams[3].Value = WTB_YIHUDRKXX.REQ_TYPE;
            //storedParams[4].Value = WTB_YIHUDRKXX.COMPANY;
            //storedParams[5].Value = WTB_YIHUDRKXX.SETTLE_METHOD;
            //storedParams[6].Value = WTB_YIHUDRKXX.YWYHDZ;
            //storedParams[7].Value = WTB_YIHUDRKXX.INCLUDE_APPLICANT;
            //storedParams[8].Value = WTB_YIHUDRKXX.PEOPLE_NUM;
            //storedParams[9].Value = WTB_YIHUDRKXX.OPER_TYPE;
            //int result = 0;
            //if (Transaction == null)
            //    result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            //else
            //    result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            //WTB_YIHUDRKXX.ID = Convert.ToInt32(storedParams[10].Value);
            //return result;
            return base.InsertOrUpdate(WTB_YIHUDRKXX, Transaction) ? 1 : 0;
        }

        public int InsertWTB_KEHUXX(WTB_KEHUXX WTB_KEHUXX,List<WTB_LIANXIXX> WTB_LIANXIXXs, OracleTransaction Transaction)
        {
            bool result=false;
            result = new WTB_KEHUXXProcess().InsertOrUpdate(WTB_KEHUXX, Transaction);
            if(result)
                foreach (WTB_LIANXIXX item in WTB_LIANXIXXs)
                {
                    result=new WTB_LIANXIXXProcess().InsertOrUpdate(item, Transaction);
                    if (!result)
                        break;
                }
            return result ? 1 : 0;
        }
        /*定义写入 发票&账单 的方法*/
        public int InsertWTB_FAPIAOXX(WTB_FAPIAOXX WTB_FAPIAOXX, OracleTransaction Transaction)
        {
            return new WTB_FAPIAOXXProcess().InsertOrUpdate(WTB_FAPIAOXX,Transaction) ? 1 : 0; //base.InsertOrUpdate(WTB_YIHUDRKXX, Transaction) ? 1 : 0;
        }
       
        /*定义写入 文件表 的方法*/
        public int InsertWTB_WENJIANXX(WTB_WENJIANXX WTB_WENJIANXX, OracleTransaction Transaction)
        {
            string spName = "CS_INSWTB_WENJIANXX";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_WENJIANXX.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_WENJIANXX.INCIDENT_ID;
            storedParams[2].Value = WTB_WENJIANXX.CERT_SOURCE;
            storedParams[3].Value = WTB_WENJIANXX.CERT_TYPE;
            storedParams[4].Value = WTB_WENJIANXX.CERT_NUMBER;
            storedParams[5].Value = WTB_WENJIANXX.FILE_UUID;
            storedParams[6].Value = WTB_WENJIANXX.FILENAME;
            storedParams[7].Value = WTB_WENJIANXX.WEB_URI;
            storedParams[8].Value = WTB_WENJIANXX.I_BEIYONG1;
            storedParams[9].Value = WTB_WENJIANXX.I_BEIYONG2;
            storedParams[10].Value = WTB_WENJIANXX.I_BEIYONG3;
            storedParams[11].Value = WTB_WENJIANXX.S_BEIYONG1;
            storedParams[12].Value = WTB_WENJIANXX.S_BEIYONG2;
            storedParams[13].Value = WTB_WENJIANXX.S_BEIYONG3;
            storedParams[14].Value = WTB_WENJIANXX.D_BEIYONG1;
            storedParams[15].Value = WTB_WENJIANXX.D_BEIYONG2;
            storedParams[16].Value = WTB_WENJIANXX.D_BEIYONG3;
            storedParams[17].Value = WTB_WENJIANXX.ID;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_WENJIANXX.ID = Convert.ToInt32(storedParams[17].Value);
            return result;
        }
        /// <summary>
        /// 新表启封、用水性质变更、拆表销户、托收办理 
        /// </summary>
        /// <param name="WTB_SHOULIXX"></param>
        /// <param name="Transaction"></param>
        /// <returns></returns>
        public int InsertWTB_SHOULIXX(WTB_SHOULIXX WTB_SHOULIXX, OracleTransaction Transaction)
        {
            string spName = "CS_InsWTB_SHOULIXX";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_SHOULIXX.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_SHOULIXX.INCIDENT_ID;
            storedParams[2].Value = WTB_SHOULIXX.CONTACT_NUM;
            storedParams[3].Value = WTB_SHOULIXX.SOURCE;
            storedParams[4].Value = WTB_SHOULIXX.BDCZL;
            storedParams[5].Value = WTB_SHOULIXX.FCZJHM;
            storedParams[6].Value = WTB_SHOULIXX.BDCDBRQ;
            storedParams[7].Value = WTB_SHOULIXX.NSRSBH;
            storedParams[8].Value = WTB_SHOULIXX.FCZJLX;
            storedParams[9].Value = WTB_SHOULIXX.BOOKTIME;
            storedParams[10].Value = WTB_SHOULIXX.WATERTYPE;
            storedParams[11].Value = WTB_SHOULIXX.REASON;
            storedParams[12].Value = WTB_SHOULIXX.COMPANY_ID;
            storedParams[13].Value = WTB_SHOULIXX.S_BEIYONG1;
            storedParams[14].Value = WTB_SHOULIXX.S_BEIYONG2;
            storedParams[15].Value = WTB_SHOULIXX.S_BEIYONG3;
            storedParams[16].Value = WTB_SHOULIXX.S_BEIYONG4;
            storedParams[17].Value = WTB_SHOULIXX.S_BEIYONG5;
            storedParams[18].Value = WTB_SHOULIXX.S_BEIYONG6;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_SHOULIXX.ID = Convert.ToInt32(storedParams[19].Value);
            return result;
        }
        /// <summary>
        /// 合用表信息
        /// </summary>
        /// <param name="WTB_HEYONGBXX"></param>
        /// <param name="Transaction"></param>
        /// <returns></returns>
        public int InsertWTB_HEYONGBXX(WTB_HEYONGBXX WTB_HEYONGBXX, OracleTransaction Transaction)
        {
            string spName = "CS_InsWTB_HEYONGBXX";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WTB_HEYONGBXX.S_ZHUBIAOBH;
            storedParams[1].Value = WTB_HEYONGBXX.INCIDENT_ID;
            storedParams[2].Value = WTB_HEYONGBXX.SOURCE;
            storedParams[3].Value = WTB_HEYONGBXX.APP_TYPE;
            storedParams[4].Value = WTB_HEYONGBXX.CONTACT_NUM;
            storedParams[5].Value = WTB_HEYONGBXX.REQ_TYPE;
            storedParams[6].Value = WTB_HEYONGBXX.COMPANY;
            storedParams[7].Value = WTB_HEYONGBXX.BOOKLETNUM;
            storedParams[8].Value = WTB_HEYONGBXX.S_BEIYONG1;
            storedParams[9].Value = WTB_HEYONGBXX.S_BEIYONG2;
            storedParams[10].Value = WTB_HEYONGBXX.S_BEIYONG3;
            storedParams[11].Value = WTB_HEYONGBXX.S_BEIYONG4;
            storedParams[12].Value = WTB_HEYONGBXX.S_BEIYONG5;
            storedParams[13].Value = WTB_HEYONGBXX.S_BEIYONG6;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            WTB_HEYONGBXX.ID = Convert.ToInt32(storedParams[14].Value);
            return result;
        }
        #endregion

        #region 其他
        /*定义读取主表记录的方法*/
        public DataTable SelectWF_YIWANGTBSJZBsDynamic(string WhereClause, string OrderByExpression, OracleTransaction Transaction)
        {
            string spName = "CS_SelWF_YIWANGTBSJZBsDyn";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = string.IsNullOrEmpty(WhereClause.Trim()) ? "1=1" : WhereClause;
            storedParams[1].Value = OrderByExpression;
            DataSet ds = null;
            if (Transaction == null)
                ds = OracleHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                ds = OracleHelper.ExecuteDataset((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            else
                return null;
        }

        /*定义添加行的方法*/
        private void AddRowNumber(DataTable dt)
        {
            dt.Columns.Add(new DataColumn("RowNumber", typeof(int)));
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i][dt.Columns.Count - 1] = i + 1;
            }
        }

        #endregion

        #region 业务办理校验
        public DataTable searchWaterUserInfo(string s_cid, string businessType,string queryType, OracleTransaction Transaction)
        {
            string spName = "WTB_searchWaterUserInfo";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = s_cid;
            storedParams[1].Value = businessType;
            storedParams[2].Value = queryType;
            DataSet ds = null;
            if (Transaction == null)
                ds = OracleHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                ds = OracleHelper.ExecuteDataset((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            else
                return null;
        }
        /// <summary>
        /// 查询未完结业务信息
        /// </summary>
        /// <param name="s_cid"></param>
        /// <param name="businessType"></param>
        /// <param name="queryType"></param>
        /// <param name="Transaction"></param>
        /// <returns></returns>
        public DataTable searchWWJYWInfo(string s_cid, string businessType, OracleTransaction Transaction)
        {
            string spName = "WTB_searchWWJYWInfo";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = s_cid;
            storedParams[1].Value = businessType;
            DataSet ds = null;
            if (Transaction == null)
                ds = OracleHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                ds = OracleHelper.ExecuteDataset((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            else
                return null;
        }
        public DataTable searchXiangTongYWInfo(string s_cid, string businessType, OracleTransaction Transaction)
        {
            string spName = "WTB_searchXiangTongYWInfo";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = s_cid;
            storedParams[1].Value = businessType;
            DataSet ds = null;
            if (Transaction == null)
                ds = OracleHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                ds = OracleHelper.ExecuteDataset((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            else
                return null;
        }
        #endregion
        #region 获取地址
        public DataTable getAddress(string s_cid, OracleTransaction Transaction)
        {
            string spName = "YHJK_GETDIZHI_WTB";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = s_cid;
            DataSet ds = null;
            if (Transaction == null)
                ds = OracleHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                ds = OracleHelper.ExecuteDataset((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            else
                return null;
        }
        #endregion
        #region 过户/实名制接口
        public DataTable KEHUXXBG(string leixing,string cid,string kehulx,string zhengjianlx,string zhengjianid,string hm,string email,string lianxir,string lianxidh,string lianxisj,string caozuor,string incident_id,OracleTransaction Transaction)
        {
            string spName = "WTB_KEHUXXBG";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = leixing;
            storedParams[1].Value = cid;
            storedParams[2].Value = kehulx;
            storedParams[3].Value = zhengjianlx;
            storedParams[4].Value = zhengjianid;
            storedParams[5].Value = hm;
            storedParams[6].Value = email;
            storedParams[7].Value = lianxir;
            storedParams[8].Value = lianxidh;
            storedParams[9].Value = lianxisj;
            storedParams[10].Value = caozuor;
            storedParams[11].Value = incident_id;
            DataSet ds = null;
            if (Transaction == null)
                ds = OracleHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                ds = OracleHelper.ExecuteDataset((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            else
                return null;
        }
        #endregion
        /// <summary>
        /// 欠费/账单查询接口
        /// </summary>
        /// <param name="tran_code"></param>
        /// <param name="date_scope"></param>
        /// <param name="bank_no"></param>
        /// <param name="oper_id"></param>
        /// <param name="fqfs"></param>
        /// <param name="s_cid"></param>
        /// <param name="i_zhangwuny"></param>
        /// <param name="s_zhangdantxm"></param>
        /// <param name="s_flowno"></param>
        /// <param name="beginny"></param>
        /// <param name="endny"></param>
        /// <returns></returns>
        public DataSet querybill(string tran_code,string date_scope,string bank_no,string oper_id, string fqfs, string s_cid, string i_zhangwuny,string s_zhangdantxm,string s_flowno, string beginny, string endny)
        {
            OracleConnection connec = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString);
            DataSet ds = null;
            using (connec)
            {
                connec.Open();
                OracleTransaction tran = connec.BeginTransaction(IsolationLevel.ReadCommitted);
                string spName = "WTB_QUERYBILLS";
                OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
                storedParams[0].Value = tran_code;
                storedParams[1].Value = date_scope;
                storedParams[2].Value = bank_no;
                storedParams[3].Value = oper_id;
                storedParams[4].Value = fqfs;
                storedParams[5].Value = s_cid;
                storedParams[6].Value = i_zhangwuny;
                storedParams[7].Value = s_zhangdantxm;
                storedParams[8].Value = s_flowno;
                storedParams[9].Value = beginny;
                storedParams[10].Value = endny;

                ds = OracleHelper.ExecuteDataset(tran, CommandType.StoredProcedure, spName, storedParams);
                tran.Commit();
            }
            if (ds != null && ds.Tables.Count > 0)
            {
                //AddRowNumber(ds.Tables[0]);
                return ds;// ds.Tables[0];
            }
            else
                return null;
        }

        public DataSet QueryHeTongXX(string incident_id)
        {
            OracleConnection connec = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString);
            DataSet ds = null;
            using (connec)
            {
                connec.Open();
                OracleTransaction tran = connec.BeginTransaction(IsolationLevel.ReadCommitted);
                string spName = "WTB_QUERYHETONGXX";
                OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
                storedParams[0].Value = incident_id;

                ds = OracleHelper.ExecuteDataset(tran, CommandType.StoredProcedure, spName, storedParams);
                tran.Commit();
            }
            if (ds != null && ds.Tables.Count > 0)
            {
                //AddRowNumber(ds.Tables[0]);
                return ds;// ds.Tables[0];
            }
            else
                return null;
        }
        public DataSet CAQueryHeTongXX(string V_S_CID, string V_S_HETONGBH, string V_S_HM, string V_S_JH, string V_D_DIANHUA, string V_S_ZHENGJIANID)
        {
            OracleConnection connec = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString);
            DataSet ds = null;
            using (connec)
            {
                connec.Open();
                OracleTransaction tran = connec.BeginTransaction(IsolationLevel.ReadCommitted);
                string spName = "CA_QUERYHETONGXX";
                OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
                storedParams[0].Value = V_S_CID;
                storedParams[1].Value = V_S_HETONGBH;
                storedParams[2].Value = V_S_HM;
                storedParams[3].Value = V_S_JH;
                storedParams[4].Value = V_D_DIANHUA;
                storedParams[5].Value = V_S_ZHENGJIANID;
                ds = OracleHelper.ExecuteDataset(tran, CommandType.StoredProcedure, spName, storedParams);
                tran.Commit();
            }
            if (ds != null && ds.Tables.Count > 0)
            {
                //AddRowNumber(ds.Tables[0]);
                return ds;// ds.Tables[0];
            }
            else
                return null;
        }
        /// <summary>
        /// 根据定义的SQL或存储过程获取数据源,改进版可以使用临时表的查询
        /// </summary>
        /// <param name="paramType"></param>
        /// <param name="sourceStr"></param>
        /// <returns></returns>
        public DataSet GetParamsDataSource(string paramType, string sourceStr, string extends)
        {
            DataSet ds = null;
            if (null == sourceStr)
                return null;

            // 存储过程数据格式
            string[] list = sourceStr.Split(';');

            if (null != list && list.Length >= 2)
            {
                OracleConnection connec = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString);
                using (connec)
                {
                    connec.Open();
                    OracleTransaction tran = connec.BeginTransaction(IsolationLevel.ReadCommitted);
                    try
                    {
                        if (paramType.Equals("<SQL>"))
                        {
                            ds = OracleHelper.ExecuteDataset(tran, CommandType.Text, list[1]);//.Tables[0];
                        }
                        else if (paramType.ToUpper().Equals("<PROCEDURE>"))
                        {
                            OracleParameter[] storedParams = Platform.Data.OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.BusinessConnString, list[1]);
                            for (int i = 0; i < storedParams.Length; i++)
                            {
                                if (storedParams[i].Direction == System.Data.ParameterDirection.Input)
                                    storedParams[i].Value = list[2 + i];
                            }

                            ds = OracleHelper.ExecuteDataset(tran, CommandType.StoredProcedure, list[1], storedParams);
                            //if (null == dst | dst.Tables.Count == 0)
                            //    return null;
                            //else
                            //    return dst.Tables[0];
                            tran.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        if (null != tran)
                        {
                            tran.Rollback();
                            throw new Exception(ex.Message.ToString());
                        }
                    }
                }

            }
            return ds;
        }
        public DataSet QueryWJMC(string ID)
        {
            OracleConnection connec = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString);
            DataSet ds = null;
            using (connec)
            {
                connec.Open();
                OracleTransaction tran = connec.BeginTransaction(IsolationLevel.ReadCommitted);
                string spName = "WTB_QueryWJMC";
                OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
                storedParams[0].Value = ID;

                ds = OracleHelper.ExecuteDataset(tran, CommandType.StoredProcedure, spName, storedParams);
                tran.Commit();
            }
            if (ds != null && ds.Tables.Count > 0)
            {
                //AddRowNumber(ds.Tables[0]);
                return ds;// ds.Tables[0];
            }
            else
                return null;
        }
        public object Transaction { get; set; }
        #region 受益人
        public int BeneficiaryInsert_AddProc(string PortType, string requestId, string errCode, string errMessage, Boolean RequestState, int StatusCode, string data, string Message, string InsertDatetime, string incidentId, OracleTransaction Transaction)
        {
            string spName = "BeneficiaryInsert_AddProc";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = PortType;
            storedParams[1].Value = requestId;
            storedParams[2].Value = errCode;
            storedParams[3].Value = errMessage;
            storedParams[4].Value = RequestState;
            storedParams[5].Value = StatusCode;
            storedParams[6].Value = data;
            storedParams[7].Value = Message;
            storedParams[8].Value = InsertDatetime;
            storedParams[9].Value = incidentId;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            int NumberID = Convert.ToInt32(storedParams[10].Value);
            return NumberID;
        }

        public int BeneficiaryQuery_MessageProc(string pid, string BanjianNumber, string Soure, string CompanyName, string CompanyCode, string HouseNumber, string UseWaterLocation, string ApplyDate, string Startdate, string Enddate, string BeneficiaryName, string BeneficiarySFZNumber, string ApplyStatus, OracleTransaction Transaction)
        {
            string spName = "BeneficiaryQuery_MessageProc";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = pid;
            storedParams[1].Value = BanjianNumber;
            storedParams[2].Value = Soure;
            storedParams[3].Value = CompanyName;
            storedParams[4].Value = CompanyCode;
            storedParams[5].Value = HouseNumber;
            storedParams[6].Value = UseWaterLocation;
            storedParams[7].Value = ApplyDate;
            storedParams[8].Value = Startdate;
            storedParams[9].Value = Enddate;
            storedParams[10].Value = BeneficiaryName;
            storedParams[11].Value = BeneficiarySFZNumber;
            storedParams[12].Value = ApplyStatus;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            int NumberID = Convert.ToInt32(storedParams[13].Value);
            return NumberID;
        }

        /// <summary>
        /// 受益人查询接口返回数据记录
        /// </summary>
        /// <param name="NumberID"></param>
        /// <param name="RequestState"></param>
        /// <param name="StatusCode"></param>
        /// <param name="Message"></param>
        /// <param name="BanjianNumber"></param>
        /// <param name="Soure"></param>
        /// <param name="CompanyName"></param>
        /// <param name="CompanyCode"></param>
        /// <param name="HouseNumber"></param>
        /// <param name="UseWaterLocation"></param>
        /// <param name="ApplyDate"></param>
        /// <param name="Startdate"></param>
        /// <param name="Enddate"></param>
        /// <param name="BeneficiaryName"></param>
        /// <param name="BeneficiarySFZNumber"></param>
        /// <param name="ApplyStatus"></param>
        /// <param name="Transaction"></param>
        /// <returns></returns>
        public int BeneficiaryQuery_QueryProc(string requestid, string errcode, string errmessage, Boolean RequestState, int StatusCode, string Message, string pid, OracleTransaction Transaction)
        {
            string spName = "BeneficiaryQuery_QueryProc";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = requestid;
            storedParams[1].Value = errcode;
            storedParams[2].Value = errmessage;
            storedParams[3].Value = RequestState;
            storedParams[4].Value = StatusCode;
            storedParams[5].Value = Message;
            storedParams[6].Value = pid;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            int NumberID = Convert.ToInt32(storedParams[7].Value);
            return NumberID;
        }
        /// <summary>
        /// 写入受益人批量查重校验表（非受益人）
        /// </summary>
        /// <param name="WTB_SYRCC"></param>
        /// <param name="Transaction"></param>
        /// <returns></returns>
        public string InsertNew_FSYRIN(string cardno, string wybs, OracleTransaction Transaction)
        {

            string spName = "New_FSYRIN";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = cardno;
            storedParams[1].Value = wybs;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            return wybs;
        }
        /// <summary>
        /// 写入受益人批量查重校验表（受益人）
        /// </summary>
        /// <param name="WTB_SYRCC"></param>
        /// <param name="Transaction"></param>
        /// <returns></returns>
        public string InsertWTB_SYRCC(string SUCCESS, string CODE, string MSG, string INCIDENTID, string SOURCE, string COMPANYNAME, string COMPANYCODE, string USERNO, string ADDRESS, string APPDATE, string STARTTIME, string ENDDATE, string BENEFICIARYNAME, string CARDNO, string APPIYSTATUS, string WYBS, OracleTransaction Transaction)
        {

            string spName = "NEW_DuplicateCheck";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = SUCCESS;
            storedParams[1].Value = CODE;
            storedParams[2].Value = MSG;
            storedParams[3].Value = INCIDENTID;
            storedParams[4].Value = SOURCE;
            storedParams[5].Value = COMPANYNAME;
            storedParams[6].Value = COMPANYCODE;
            storedParams[7].Value = USERNO;
            storedParams[8].Value = ADDRESS;
            storedParams[9].Value = APPDATE;
            storedParams[10].Value = STARTTIME;
            storedParams[11].Value = ENDDATE;
            storedParams[12].Value = BENEFICIARYNAME;
            storedParams[13].Value = CARDNO;
            storedParams[14].Value = APPIYSTATUS;
            storedParams[15].Value = WYBS;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            return WYBS;
        }
        public DataSet GETHDBJ(string S_KAISHISJ, string S_JIESHUSJ, OracleTransaction Transaction)
        {
            OracleConnection connec = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString);
            DataSet ds = null;
            using (connec)
            {
                connec.Open();
                OracleTransaction tran = connec.BeginTransaction(IsolationLevel.ReadCommitted);
                string spName = "GETHDBJ";
                OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
                storedParams[0].Value = S_KAISHISJ;
                storedParams[1].Value = S_JIESHUSJ;
                ds = OracleHelper.ExecuteDataset(tran, CommandType.StoredProcedure, spName, storedParams);
                tran.Commit();
            }
            if (ds != null && ds.Tables.Count > 0)
            {
                //AddRowNumber(ds.Tables[0]);
                return ds;// ds.Tables[0];
            }
            else
                return null;
        }
        /// <summary>
        /// 每日办件数量核对表添加
        /// </summary>
        /// <param name="WTB_HEYONGBXX"></param>
        /// <param name="Transaction"></param>
        /// <returns></returns>
        public string InsertWTB_BJSLHD(string SUCCESS, string CODE, string MSG, string INCIDENTID, string USERNO, string wybs, OracleTransaction Transaction)
        {
            string spName = "New_NumberCheckIN";
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = SUCCESS;
            storedParams[1].Value = CODE;
            storedParams[2].Value = MSG;
            storedParams[3].Value = INCIDENTID;
            storedParams[4].Value = USERNO;
            storedParams[5].Value = wybs;
            int result = 0;
            if (Transaction == null)
                result = OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = OracleHelper.ExecuteNonQuery((OracleTransaction)Transaction, CommandType.StoredProcedure, spName, storedParams);
            return wybs;
        }
        #endregion
    }
}